package us.kg.kotoriforest.advance;

import org.junit.Test;
import us.kg.kotoriforest.advance.pojo.Employee;

import java.sql.*;
import java.util.ArrayList;

public class AdvanceTest {
    @Test
    public void test1() {
        ArrayList<Employee> employees = new ArrayList<>();

        // 注册驱动

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM t_emp WHERE emp_age > ?")) {

            preparedStatement.setInt(1, 25);


            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    int empId = resultSet.getInt("emp_id");
                    ;
                    String empName = resultSet.getString("emp_name");
                    double empSalary = resultSet.getDouble("emp_salary");
                    int empAge = resultSet.getInt("emp_age");

                    employees.add(new Employee(empId, empName, empSalary, empAge));
                }
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        employees.forEach(System.out::println);
    }

    @Test
    public void test2() {
        // 主键回显

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_emp(emp_name, emp_salary, emp_age) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {

            // 输入数据
            Employee employee = new Employee(0, "Rose", 1919.81, 26);

            // 用数据替换掉占位符
            preparedStatement.setString(1, employee.getEmpName());
            preparedStatement.setDouble(2, employee.getEmpSalary());
            preparedStatement.setInt(3, employee.getEmpAge());

            // 执行 SQL 语句
            int rows = preparedStatement.executeUpdate();

            // 判断成功与否
            if (rows > 0) {
                // 得到返回的主键并赋值

                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {

                    if (generatedKeys.next()) { // 因为这里我们只插入了一条数据 所以一定是单行的
                        employee.setEmpId(generatedKeys.getInt(1)); // 因为返回的只有主键值 所以一定是单列的
                        System.out.println(employee);
                    }
                }
            } else {
                System.out.println("fail");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);

        }
    }

    @Test
    public void test3() {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC?rewriteBatchedStatements=true", "root", "")) {
            /*
             * 进行批量操作的前提:
             *   在连接数据库的 url 后加上参数 rewriteBatchedStatements=true
             *   SQL 语句中必须使用 VALUES 而不是 VALUE
             *   SQL 语句不能以分号结尾
             *   调用 addBatch() 添加批处理操作
             *   调用 executeBatch() 执行批处理操作
             * */

            String SQL = "INSERT INTO t_emp(emp_name, emp_salary, emp_age) VALUES (?, ?, ?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(SQL)) {
                long begin = System.currentTimeMillis();

                for (int i = 1; i <= 10000; i++) { // 插入 10000 条数据
                    preparedStatement.setString(1, "emp-" + i);
                    preparedStatement.setDouble(2, 1111);
                    preparedStatement.setInt(3, 35);

                    // 添加批处理
                    preparedStatement.addBatch();
                }

                // 执行批处理
                preparedStatement.executeBatch();

                long end = System.currentTimeMillis();
                System.out.println("cost " + (end - begin) + " ms");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
